﻿package com.dbm.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.dbm.model.ConnPoolFactory;
import com.dbm.model.OracleFunctionUtil;
import com.dbm.util.PackageInfo;
import com.dbm.util.PackageItem;
import com.dbm.util.Response;
import com.dbm.util.SellSoInfoUtil;

public class WmsSellSoInfoDao {
	Logger log = Logger
			.getLogger("sellSO数据处理DAO----com.gotop.dao.ctldao.WmsSoInfoDao");
	
	
	/**
	 * 判断订单是是否已经进入 wms的 doc_order_header 
	 * @param soreference1
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean judgeOrder(String soreference1,String wmscustomerid)throws Exception{
		boolean flag = false;
		Connection wmsConn = ConnPoolFactory.getWMSConnection();
		String sql = "SELECT COUNT(*) as nums FROM DOC_ORDER_HEADER H WHERE H.soreference1=? AND H.CUSTOMERID=? ";
		PreparedStatement stat = wmsConn.prepareStatement(sql);
		stat.setString(1, soreference1);
		stat.setString(2, wmscustomerid);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			if(rs.getInt("nums")>0){
				flag = true;
			}
		}
		rs.close();
		stat.close();
		wmsConn.close();
		System.out.println("判断订单是否进入wms：\t"+flag);
		return flag;
	}
	
	/**
	 * 将出库确认返回的报文解析后写入 wms 数据库！
	 * @param response
	 * @param orderno
	 * @throws Exception
	 */
	public synchronized void addEdiInfo(Response response,String orderno) throws Exception{
		Connection wmsConn = ConnPoolFactory.getWMSConnection();
		String sql = "update doc_order_header h set h.h_edi_06=?,h.h_edi_07=?,h.h_edi_08=? " +
				"where h.soreference1=? and h.warehouseid=? and h.customerid=?";
		PreparedStatement stat = wmsConn.prepareStatement(sql);
		stat.setString(1, response.getCode());
		stat.setString(2, response.getFlag());
		stat.setString(3, response.getMessage());
		stat.setString(4, orderno);
		stat.setString(5, response.getWarehouseId());
		stat.setString(6, response.getOwnercode());
		stat.executeUpdate();
		wmsConn.close();
		wmsConn.close();
	}
	
	/**
	 * 根据 soreference1 获取orderno
	 * @param soreference1
	 * @return
	 * @throws Exception
	 */
	public synchronized String getSoNoBySoreference1(String soreference1) throws Exception{
		String soNo = "";
		Connection wmsconn = ConnPoolFactory.getWMSConnection();
		String sql = "SELECT orderno from doc_order_header h where h.soreference1=?";
		PreparedStatement statement = wmsconn.prepareStatement(sql);
		statement.setString(1, soreference1);
		ResultSet rs = statement.executeQuery();
		if(rs.next()){
			soNo = rs.getString("orderno");
		}
		rs.close();
		statement.close();
		wmsconn.close();
		return soNo;
	}
	/**
	 * 重置wms 数据库 doc_order_header 表的发送标记
	 * @param flagvalue
	 * @param orderno
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean updateSoHeaderSendFlag(String flagvalue,String orderno) throws Exception{
		boolean flag = false;
		Connection wmsconn = ConnPoolFactory.getWMSConnection(); 
		String upOrderHeaderSql = "update DOC_ORDER_HEADER H SET H.EDISENDFLAG=?,H.EDISENDTIME=SYSDATE where h.orderno=?";
		PreparedStatement statement = wmsconn.prepareStatement(upOrderHeaderSql);
		statement.setString(1, flagvalue);
		statement.setString(2, orderno);
		log.info("更新SQL："+upOrderHeaderSql+"\t标记："+flagvalue+"\t发货单号："+orderno);
		if(statement.executeUpdate()==1){
			flag = true;
		}
		statement.close();
		wmsconn.close();
		return flag;
	}
	
	/**
	 * 根据运单号获取包裹明细信息
	 * @param packageinfo
	 * @return
	 * @throws Exception
	 */
	public synchronized List<PackageItem> getPackageItemByPackageInfo(PackageInfo packageinfo) throws Exception{
		List<PackageItem> packageItems = new ArrayList<PackageItem>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection();
		String sql = "SELECT D.Sku, d.qtyshipped_each ,d.customerid ,d.picktotraceid," +
       " case  when nvl(h.userdefine1, 'gotop') = 'gotop' then  h.carrierid else h.userdefine1   end as logisticsName,"+
       " d.grossweight FROM ACT_ALLOCATION_DETAILS D LEFT JOIN DOC_ORDER_HEADER H ON D.ORDERNO = H.ORDERNO" +
       " WHERE D.picktotraceid =?";
		PreparedStatement stat = wmsconn.prepareStatement(sql);
		stat.setString(1, packageinfo.getEXPRESSCODE());
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			PackageItem packageItem = new PackageItem();
			packageItem.setITEMCODE(rs.getString("sku"));
			packageItem.setITEMID(rs.getString("sku"));
			packageItem.setOWNERCODE(rs.getString("customerid"));
			packageItem.setQUANTITY(rs.getBigDecimal("qtyshipped_each"));
			packageItems.add(packageItem);
		}
		rs.close();
		stat.close();
		wmsconn.close();
		return packageItems;
	}
	/**
	 * 根据客户订单号，查出所有的包裹信息
	 * @param soreference1
	 * @return
	 * @throws Exception
	 */
	public synchronized List<PackageInfo> getAllPackageInfo(String soreference1) throws Exception{
		List<PackageInfo> packageInfos = new ArrayList<PackageInfo>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection();
		String sql = "select a.picktotraceid, a.LOGISTICSCODE, a.logisticsName, sum(a.grossweight) as weight "+
				"from (SELECT d.picktotraceid, " +
				"case when nvl(h.userdefine1, 'gotop') = 'gotop' then  h.carrierid else h.userdefine1  end as LOGISTICSCODE, "+
				"case when nvl(h.userdefine2, 'gotop') = 'gotop' then  h.carriername else h.userdefine2 end as logisticsName, "+
				"d.grossweight " +
				"FROM ACT_ALLOCATION_DETAILS D LEFT JOIN DOC_ORDER_HEADER H ON D.ORDERNO = H.ORDERNO " +
				"WHERE H.SOREFERENCE1 = ?) a group by a.picktotraceid, a.LOGISTICSCODE, a.logisticsName";
		PreparedStatement stat = wmsconn.prepareStatement(sql);
		stat.setString(1, soreference1);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			PackageInfo packageinfo = new PackageInfo();
			packageinfo.setEXPRESSCODE(rs.getString("picktotraceid"));
			packageinfo.setLOGISTICSCODE(rs.getString("LOGISTICSCODE"));
			packageinfo.setLOGISTICSNAME(rs.getString("logisticsName"));
			packageinfo.setWEIGHT(rs.getBigDecimal("weight"));
			packageInfos.add(packageinfo);
		}
		rs.close();
		stat.close();
		wmsconn.close();
		return packageInfos;
	}
	
	
	/**
	 * 从 wms 系统获取所有已发货（含部分发货）的客户订单号-package
	 * @return
	 * @throws Exception
	 */
	public synchronized List<String> getAllReturnSoreferenceInfoPackage() throws Exception{
		List<String> soreference1s = new ArrayList<String>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection(); 
		String getOrderCodeSql = "SELECT distinct t.soreference1 FROM ctl_sostatus_fhd_package t";
		PreparedStatement statement = wmsconn.prepareStatement(getOrderCodeSql);
		ResultSet rs = statement.executeQuery();
		while(rs.next()){
			String soreference1=rs.getString("soreference1");
			soreference1s.add(soreference1);
		}
		rs.close();
		statement.close();
		wmsconn.close();
		return soreference1s;
	}
	
	
	
	/**
	 * 从 wms 系统获取所有已发货（含部分发货）的客户订单号
	 * @return
	 * @throws Exception
	 */
	public synchronized List<String> getAllReturnSoreferenceInfo() throws Exception{
		List<String> soreference1s = new ArrayList<String>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection(); 
		String getOrderCodeSql = "SELECT distinct t.soreference1 FROM ctl_sostatus_fhd t";
		PreparedStatement statement = wmsconn.prepareStatement(getOrderCodeSql);
		ResultSet rs = statement.executeQuery();
		while(rs.next()){
			String soreference1=rs.getString("soreference1");
			soreference1s.add(soreference1);
		}
		rs.close();
		statement.close();
		wmsconn.close();
		return soreference1s;
	}
	
	
	/**
	 * 获取发货状态的数据（完全发货）
	 * @return
	 * @throws Exception
	 */
	public synchronized List<SellSoInfoUtil> getReturnSoInfoFromWmsPackage(String soreference1) throws Exception{
		List<SellSoInfoUtil> soInfos = new ArrayList<SellSoInfoUtil>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection(); 
		String returnSoSql = "SELECT * FROM CTL_SOSTATUS_FHD_package t " +
				"where t.SOSTATUS in ('99') " +
				"and t.qtyshipped_each>'0' and t.soreference1=? and t.edisendflag in ('7','N')";
		PreparedStatement statement = wmsconn.prepareStatement(returnSoSql);
		statement.setString(1, soreference1);
		log.info("完全发货数据查询："+returnSoSql+"--"+soreference1);
		ResultSet rs = statement.executeQuery();
		while(rs.next()){
			SellSoInfoUtil soInfo = new SellSoInfoUtil();
			soInfo.setORDERNO(rs.getString("orderno"));
			soInfo.setSTATUS(rs.getString("SOSTATUS"));
			soInfo.setITEMCODE(rs.getString("SKU"));
			soInfo.setITEMID(rs.getString("SKU"));
			soInfo.setDELIVERYORDERCODE(rs.getString("soreference1"));
			soInfo.setPLANQTY(rs.getBigDecimal("qtyordered"));
			soInfo.setACTUALQTY(rs.getBigDecimal("qtyshipped_each"));
			soInfo.setEXPRESSCODE("PICKTOTRACEID");
			soInfo.setOWNERCODE(rs.getString("customerid"));
			soInfo.setWAREHOUSECODE(rs.getString("warehouseid"));
			soInfos.add(soInfo);
		}
		rs.close();
		statement.close();
		wmsconn.close();
		return soInfos;
	}
	
	/**
	 * 获取发货状态的数据（完全发货）
	 * @return
	 * @throws Exception
	 */
	public synchronized List<SellSoInfoUtil> getReturnSoInfoFromWmsEnd(String soreference1) throws Exception{
		List<SellSoInfoUtil> soInfos = new ArrayList<SellSoInfoUtil>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection(); 
		String returnSoSql = "SELECT * FROM CTL_SOSTATUS_FHD t " +
				"where t.SOSTATUS in ('99') " +
				"and t.qtyshipped_each>'0' and t.soreference1=? and t.edisendflag in ('7','N')";
		PreparedStatement statement = wmsconn.prepareStatement(returnSoSql);
		statement.setString(1, soreference1);
		log.info("完全发货数据查询："+returnSoSql+"--"+soreference1);
		ResultSet rs = statement.executeQuery();
		while(rs.next()){
			SellSoInfoUtil soInfo = new SellSoInfoUtil();
			soInfo.setORDERNO(rs.getString("orderno"));
			soInfo.setSTATUS(rs.getString("SOSTATUS"));
			soInfo.setITEMCODE(rs.getString("SKU"));
			soInfo.setITEMID(rs.getString("SKU"));
			soInfo.setDELIVERYORDERCODE(rs.getString("soreference1"));
			soInfo.setPLANQTY(rs.getBigDecimal("qtyordered"));
			soInfo.setACTUALQTY(rs.getBigDecimal("qtyshipped_each"));
			soInfo.setEXPRESSCODE("PICKTOTRACEID");
			soInfo.setOWNERCODE(rs.getString("customerid"));
			soInfo.setWAREHOUSECODE(rs.getString("warehouseid"));
			soInfos.add(soInfo);
		}
		rs.close();
		statement.close();
		wmsconn.close();
		return soInfos;
	}
	/**
	 * 获取发货状态的数据（部分发货）
	 * @return
	 * @throws Exception
	 */
	public synchronized List<SellSoInfoUtil> getReturnSoInfoFromWms(String soreference1) throws Exception{
		List<SellSoInfoUtil> soInfos = new ArrayList<SellSoInfoUtil>();
		Connection wmsconn = ConnPoolFactory.getWMSConnection(); 
		String returnSoSql = "SELECT * FROM CTL_SOSTATUS t " +
				"where t.SOSTATUS in ('70') " +
				"and t.qtyshipped_each>'0' and t.soreference1=? and (t.edisendflag < '7' or t.edisendflag='N') " +
				"and rownum <=10";
		PreparedStatement statement = wmsconn.prepareStatement(returnSoSql);
		statement.setString(1, soreference1);
		ResultSet rs = statement.executeQuery();
		while(rs.next()){
			SellSoInfoUtil soInfo = new SellSoInfoUtil();
			soInfo.setORDERNO(rs.getString("orderno"));
			soInfo.setSTATUS(rs.getString("SOSTATUS"));
			soInfo.setITEMCODE(rs.getString("SKU"));
			soInfo.setITEMID(rs.getString("SKU"));
			soInfo.setDELIVERYORDERCODE(rs.getString("soreference1"));
			soInfo.setPLANQTY(rs.getBigDecimal("qtyordered"));
			soInfo.setACTUALQTY(rs.getBigDecimal("qtyshipped_each"));
			soInfo.setEXPRESSCODE("PICKTOTRACEID");
			soInfos.add(soInfo);
		}
		rs.close();
		statement.close();
		wmsconn.close();
		return soInfos;
	}
	
	
	/**
	 * 新增SO 信息到 WMS 系统
	 * @param soInfos
	 */
	public synchronized boolean addSellSoInfoToWms(List<SellSoInfoUtil> sellSoInfoUtils) throws Exception{
		boolean flag = false;
		String wmsProSql = "call spcom_getidsequence(?,?,?,?,?)";
			Connection wmsconn = ConnPoolFactory.getWMSConnection();
			CallableStatement statement = wmsconn.prepareCall(wmsProSql);
			SellSoInfoUtil soInfo = sellSoInfoUtils.get(0);
			statement.setString(1, soInfo.getWAREHOUSECODE());
			statement.setString(2, "cn");
			statement.setString(3, "ORDERNO");
			statement.registerOutParameter(4, Types.VARCHAR);
			statement.registerOutParameter(5, Types.VARCHAR);
			statement.execute();
			wmsconn.commit();
			String soNo = statement.getString(4);
			statement.close();
			System.out.println("根据序列获取到的SO单号："+soNo+"\nSoInfo输出：\n"+soInfo);
			String soHeaderSql = "insert into DOC_ORDER_HEADER "
					+ "(ORDERNO, CUSTOMERID, SOREFERENCE1, ORDERTIME, EXPECTEDSHIPMENTTIME1, "
					+ "EXPECTEDSHIPMENTTIME2, REQUIREDDELIVERYTIME, PRIORITY, CONSIGNEEID, C_CONTACT,"
					+ " CONSIGNEENAME, C_ADDRESS1, C_ADDRESS2, C_ADDRESS3, C_ADDRESS4, "
					+ "C_CITY, C_PROVINCE, C_COUNTRY, C_FAX, BILLINGID, "
					+ "C_ZIP, B_CONTACT, BILLINGNAME, B_ADDRESS1, B_ADDRESS2, "
					+ "B_ADDRESS3, B_ADDRESS4, B_CITY, B_PROVINCE, B_ZIP, "
					+ "B_COUNTRY, DELIVERYTERMSDESCR, B_FAX, B_EMAIL, PAYMENTTERMSDESCR, "
					+ "DELIVERYTERMS, PAYMENTTERMS, DOOR, ROUTE, STOP, "
					+ "SOSTATUS, PLACEOFDISCHARGE, PLACEOFDELIVERY, ORDERTYPE, USERDEFINE1, "
					+ "USERDEFINE2, USERDEFINE3, USERDEFINE4, USERDEFINE5, ADDTIME, "
					+ "ADDWHO, EDITTIME, EDITWHO, NOTES, SOREFERENCE2, "
					+ "SOREFERENCE3, CARRIERID, CARRIERNAME, CARRIERADDRESS1, CARRIERADDRESS3, "
					+ "CARRIERADDRESS2, CARRIERADDRESS4, CARRIERCITY, CARRIERPROVINCE, CARRIERCOUNTRY, "
					+ "CARRIERZIP, ISSUEPARTYID, ISSUEPARTYNAME, I_ADDRESS1, I_ADDRESS2, "
					+ "I_ADDRESS3, I_ADDRESS4, I_CITY, I_PROVINCE, I_COUNTRY, "
					+ "I_ZIP, LASTSHIPMENTTIME, EDISENDFLAG, PICKING_PRINT_FLAG, CREATESOURCE, "
					+ "EDISENDTIME, EDISENDTIME2, EDISENDTIME3, EDISENDTIME4, EDISENDTIME5, "
					+ "B_TEL1, B_TEL2, CARRIERCONTACT, CARRIERMAIL, CARRIERFAX, "
					+ "CARRIERTEL1, CARRIERTEL2, C_MAIL, C_TEL1, C_TEL2, "
					+ "I_CONTACT, I_MAIL, I_FAX, I_TEL1, I_TEL2, "
					+ "RELEASESTATUS, TRANSPORTATION, SOREFERENCE4, SOREFERENCE5, H_EDI_01, "
					+ "H_EDI_02, H_EDI_03, H_EDI_04, H_EDI_05, H_EDI_06, "
					+ "H_EDI_07, H_EDI_08, H_EDI_09, H_EDI_10, USERDEFINE6, "
					+ "ORDER_PRINT_FLAG, RFGETTASK, WAREHOUSEID, ERPCANCELFLAG, ZONEGROUP, "
					+ "MEDICALXMLTIME, PLACEOFLOADING, REQUIREDELIVERYNO, SINGLEMATCH, SERIALNOCATCH, "
					+ "FOLLOWUP, USERDEFINEA, USERDEFINEB, SALESORDERNO, INVOICEPRINTFLAG, "
					+ "INVOICENO, INVOICETITLE, INVOICETYPE, INVOICEITEM, INVOICEAMOUNT, "
					+ "ARCHIVEFLAG, CONSIGNEENAME_E, PUTTOLOCATION, FUL_ALC, DELIVERYNO, "
					+ "CHANNEL, WAVENO, ALLOCATIONCOUNT, EXPRESSPRINTFLAG, DELIVERYNOTEPRINTFLAG, "
					+ "WEIGHTINGFLAG, UDFPRINTFLAG1, UDFPRINTFLAG2, UDFPRINTFLAG3, CARTONGROUP, "
					+ "CARTONID)" + "values "
					+ "(?, ?, ?, sysdate, sysdate, "
					+ "null, null, '1', ?, ?, " 
					+ "?, ?, ?, ?, null, "
					+ "?,?,?, null, null, " 
					+ "?, null, null, null, null, "
					+ "null, null, null, null, null, "
					+ "null, null, null, null, null, "
					+ "null, null, null, null, null, "
					+ "'00', null, null, ?, null, "
					+ "null, ?, null, null,sysdate, "
					+ "'ctl', sysdate, 'ctl', ?, ?, "
					+ "?, ?, ?, null, null, "
					+ "null, null, ?, null, null, "
					+ "null, null, null, null, null, "
					+ "null, null, null, null, null, "
					+ "null, sysdate, 'Y', 'N', null, "
					+ "sysdate, null, null, null, sysdate, "
					+ "null, null, null, null, null, "
					+ "null, null, ?, ?, ?, "
					+ "?, null, null, null, null, "
					+ "'Y', null, null, null, null, "
					+ "null, null, null, null, null, "
					+ "null, 'sellSo', null, null, null, "
					+ "'N', 'N', ?, 'N', null, "
					+ "null, null, null, 'N', 'Y', "
					+ "null, null, null, null, null, "
					+ "null, null, null, null, null, "
					+ "'N', null, null, null, ?, "
					+ "'*', '*', 0, 'N', 'N', " 
					+ "'N', 'N', 'N', 'N', null, "
					+ "null)";
			PreparedStatement soheastat = wmsconn.prepareStatement(soHeaderSql);
			soheastat.setString(1, soNo);
			soheastat.setString(2, soInfo.getOWNERCODE());
			soheastat.setString(3, soInfo.getDELIVERYORDERCODE());
			soheastat.setString(4, soInfo.getRECEIVERINFOID());
			soheastat.setString(5, soInfo.getNAME());
			soheastat.setString(6, soInfo.getBUYERNICK());
			soheastat.setString(7, soInfo.getDETAILADDRESS());
			soheastat.setString(8, soInfo.getAREA());
			soheastat.setString(9, soInfo.getTOWN());
			soheastat.setString(10, soInfo.getCITY());
			soheastat.setString(11, soInfo.getPROVINCE());
			soheastat.setString(12, "");
			soheastat.setString(13, soInfo.getZIPCODE());
			soheastat.setString(14, soInfo.getORDERTYPE());
			soheastat.setString(15, soInfo.getBUYERMESSAGE());//买家用户留言写入用户自定义三
			soheastat.setString(16, soInfo.getREMARK());//备注写入 note字段
			soheastat.setString(17, "FHD");
			soheastat.setString(18, soInfo.getEXPRESSCODE());
			soheastat.setString(19, soInfo.getLOGISTICSCODE());
			soheastat.setString(20, soInfo.getLOGISTICSNAME());
			soheastat.setString(21, soInfo.getLOGISTICSAREACODE());
			soheastat.setString(22, soInfo.getEMAIL());
			soheastat.setString(23, soInfo.getTEL());
			soheastat.setString(24, soInfo.getMOBILE());
			soheastat.setString(25, soInfo.getSHOPNICK());
			soheastat.setString(26, soInfo.getWAREHOUSECODE());
			soheastat.setString(27, soInfo.getEXPRESSCODE());
			soheastat.executeUpdate();
			wmsconn.commit();
			int i = 1;
			String a = OracleFunctionUtil.getSysConfiguration(soInfo.getWAREHOUSECODE(), soInfo.getOWNERCODE(), "TOP_QM_SKU");
			for (SellSoInfoUtil soInfode : sellSoInfoUtils) {
				String soDeSql = "insert into doc_order_details "
						+ "(ORDERNO, ORDERLINENO, CUSTOMERID, SKU, LINESTATUS, "
						+ "LOTNUM, LOTATT01, LOTATT02, LOTATT03, LOTATT04, "
						+ "LOTATT05, LOTATT06, LOTATT07, LOTATT08, LOTATT09, "
						+ "LOTATT10, LOTATT11, LOTATT12, PICKZONE, LOCATION, "
						+ "TRACEID, QTYORDERED, QTYSOFTALLOCATED, QTYALLOCATED, QTYPICKED, "
						+ "QTYSHIPPED, UOM, PACKID, SOFTALLOCATIONRULE, ALLOCATIONRULE, "
						+ "USERDEFINE1, USERDEFINE2, USERDEFINE3, USERDEFINE4, USERDEFINE5, "
						+ "NOTES, QTYORDERED_EACH, QTYSOFTALLOCATED_EACH, QTYALLOCATED_EACH, QTYPICKED_EACH, "
						+ "QTYSHIPPED_EACH, ADDTIME, ADDWHO, EDITTIME, EDITWHO, "
						+ "ROTATIONID, NETWEIGHT, GROSSWEIGHT, CUBIC, PRICE, "
						+ "ALTERNATIVESKU, KITREFERENCENO, D_EDI_01, D_EDI_02, D_EDI_03, "
						+ "D_EDI_04, D_EDI_05, D_EDI_06, D_EDI_07, D_EDI_08, "
						+ "D_EDI_09, D_EDI_10, ORDERLINEREFERENCENO, D_EDI_11, D_EDI_12, "
						+ "D_EDI_13, D_EDI_14, D_EDI_15, D_EDI_16, D_EDI_17, "
						+ "D_EDI_18, D_EDI_19, D_EDI_20, KITSKU, ERPCANCELFLAG, "
						+ "USERDEFINE6, ZONEGROUP, LOCGROUP1, LOCGROUP2, COMMINGLESKU, "
						+ "ONESTEPALLOCATION, ORDERLOTCONTROL, FULLCASELOTCONTROL, PIECELOTCONTROL, REFERENCELINENO, "
						+ "SALESORDERNO, SALESORDERLINENO, QTYRELEASED, FREEGIFT, SDJM)"
						+ " values "
						+ "(?, ?, ?, ?, '00', "
						+ "null, null, null, null, null, "
						+ "null, null, null, null, null, "
						+ "null, null, null, null, null, "
						+ "'*', ?, 1.00000000, 1.00000000, 0.00000000, "
						+ "1.00000000, 'EA', 'STANDARD', 'STANDARD', 'STANDARD', "
						+ "null, null, null, null, null, "
						+ "null, ?, 1.00000000, 1.00000000, 0.00000000, "
						+ "1.00000000, sysdate, 'system', sysdate, 'ctl', "
						+ "'STANDARD', 0.00000000, 1.08000000, 1.00000000, 21.00000000, "
						+ "null, null, null, null, null, "
						+ "null, null, '0', null, null, "
						+ "null, 0.00000000, null, null, null, "
						+ "null, null, null, null, null, "
						+ "null, null, null, null, 'N', "
						+ "null, null, null, null, 'Y', "
						+ "null, 'N', 'N', 'N', null, "
						+ "null, '1', null, 'N', null)";
				PreparedStatement sodetStat = wmsconn.prepareStatement(soDeSql);
				sodetStat.setString(1, soNo);
				sodetStat.setInt(2, i);
				sodetStat.setString(3, soInfode.getOWNERCODE());
				if("CODE".equals(a.toUpperCase()))
				sodetStat.setString(4, soInfode.getITEMCODE());
				if("ID".equals(a.toUpperCase()))
				sodetStat.setString(4, soInfode.getITEMID());	
				sodetStat.setBigDecimal(5, soInfode.getPLANQTY());
				sodetStat.setBigDecimal(6, soInfode.getPLANQTY());
				sodetStat.executeUpdate();
				i++;
				wmsconn.commit();
				sodetStat.close();
			}
			String asnendPro = "call CTL_TO_WMS_SO(?,?,?,?)";
			CallableStatement asnprostat =wmsconn.prepareCall(asnendPro);
			asnprostat.setString(1, soInfo.getWAREHOUSECODE());
			asnprostat.setString(2, soInfo.getOWNERCODE());
			asnprostat.setString(3, soNo);
			asnprostat.registerOutParameter(4, Types.VARCHAR);
			asnprostat.execute();
			wmsconn.commit();
			log.info("warehouseId:\t"+soInfo.getWAREHOUSECODE()+"OwnerCode:\t"+soInfo.getOWNERCODE()+"AsnNo:\t"+soNo+"sellso新增存储过程调用结果:\t"+asnprostat.getString(4));
			soheastat.close();
			asnprostat.close();
			wmsconn.close();
			log.info("出库单处理成功！");
			soNo = null;
			flag = true;
		return flag;
	}


}
